package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.dto.CampEmpAppd;
import com.telecom.cms.dto.CampEmpAppdEx;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class CampEmpAppdDAO {
	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampEmpAppd transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db + ".Camp_Emp_Appd where camp_Id="
				+ transientInstance.getCampId()+" and camp_seq_id="
				+ transientInstance.getCampSeqId()+" and user_id="
				+ transientInstance.getUserId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Camp_Emp_Appd set latn_id='"
						+ transientInstance.getLatnId() + "',camp_id='"
						+ transientInstance.getCampId() + "',camp_seq_id='"
						+ transientInstance.getCampSeqId() + "',user_id='"
						+ transientInstance.getUserId() + "',User_Cust_Num="
						+ transientInstance.getUserCustNum() + ",Creator_Name='"
						+ transientInstance.getCreatorName() + "',Create_Time=timestamp '"
						+ CurrentTime.getCurrentTime()
						+ "' where camp_Id="
						+ transientInstance.getCampId()+" and camp_seq_id="
						+ transientInstance.getCampSeqId()+" and user_id="
						+ transientInstance.getUserId();
			else
				sql = "insert into " + db + ".Camp_Emp_Appd values('"
						+ transientInstance.getLatnId() + "','"
						+ transientInstance.getCampId() + "','"
						+ transientInstance.getCampSeqId() + "','"
						+ transientInstance.getUserId() + "',"
						+ transientInstance.getUserCustNum()+",'"
						+ transientInstance.getCreatorName() + "',timestamp '"
						+ CurrentTime.getCurrentTime() + "')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(CampEmpAppd persistentInstance) {

		String sql = "delete from " + db + ".Camp_Emp_Appd where camp_Id = "
				+ persistentInstance.getCampId()+" and camp_seq_id="
				+persistentInstance.getCampSeqId()+" and user_id="
				+persistentInstance.getUserId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void delete(String campid,String campseqid) {

		String sql = "delete from " + db + ".Camp_Emp_Appd where camp_Id = "
				+ campid+" and camp_seq_id="
				+campseqid;

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public CampEmpAppd findById(String campid,String campseqid,String userid) {

		String sql = "select * from " + db + ".Camp_Emp_Appd where camp_Id = "
				+ campid+" and camp_seq_id="+campseqid
				+ " and user_id="+userid;
		CampEmpAppd sd = new CampEmpAppd();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setUserId(rs.getInt(4));
				sd.setUserCustNum(rs.getInt(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".Camp_Emp_Appd";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampEmpAppd sd = new CampEmpAppd();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setUserId(rs.getInt(4));
				sd.setUserCustNum(rs.getInt(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAllByUser(String userid) {

		String sql = null;
		
			sql="select distinct a.camp_id,a.camp_seq_id,b.camp_name,c.camp_seq_name from " + db + ".camp_emp_appd a "
			+" left join " + db + ".camp_camp b "
			+" on a.camp_id=b.camp_id "
			+" inner join " + db + ".camp_camp_seq c "
			+" on a.camp_seq_id=c.camp_seq_id and c.is_Camp_Start='Y' "
			+" where a.user_id="+userid;
		
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampEmpAppdEx sd = new CampEmpAppdEx();
				sd.setCampId(rs.getInt(1));
				sd.setCampSeqId(rs.getInt(2));
				sd.setCampName(rs.getString(3));
				sd.setCampSeqName(rs.getString(4));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAll(String campid,String campseqid) {
		String sql = "select a.*,b.user_name from " + db + ".Camp_Emp_Appd a," + db + ".Sys_User b where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id=b.user_id";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampEmpAppdEx sd = new CampEmpAppdEx();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setUserId(rs.getInt(4));
				sd.setUserCustNum(rs.getInt(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
				sd.setUserName(rs.getString(8));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}

}
